package com.sxfq.loansupermarket.backsystem.modular.basicData.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.annotion.log.BussinessLog;
import com.sxfq.loansupermarket.backsystem.common.constant.Dict;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.ConstantFactory;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.constant.tips.Tip;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.common.node.ZTreeNode;
import com.sxfq.loansupermarket.backsystem.common.persistence.dao.MPayServiceMapper;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.PayService;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.LogObjectHolder;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.dao.PayServiceDao;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.GetIdInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServicedInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.SearchPayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.service.IPayServiceService;
import com.sxfq.loansupermarket.backsystem.modular.system.warpper.ChannelWarpper;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.DepartmentInfo;
import net.sf.json.JSONObject;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * 科室管理
 *
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/payService")
public class PayServiceController extends BaseController {
    @Autowired
    IPayServiceService service;

    @Autowired
    ICustomerService customerService;

    @Autowired
    private PayServiceDao dao;

    @Autowired
    MPayServiceMapper mPayServiceMapper;

    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/basicData/payService/";

    /**
     * 跳转到科室管理页面
     */
    @RequestMapping("")
    public String payService(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + ShiroKit.getUser().getPay_service_grade_info() + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        return PREFIX + "payService.html";
    }

    /**
     * 跳转到预约客户管理添加页面
     */
    @RequestMapping(value = "/payService_add")
    public String payServiceAdd(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + ShiroKit.getUser().getPay_service_grade_info() + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        return PREFIX + "payService_add.html";
    }

    @RequestMapping(value = "/payServiceImprot")
    public String payServiceImprot() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "payService_improt.html";
    }

    /**
     * 删除
     */
    @BussinessLog(value = "删除项目", key = "payServiceId", dict = Dict.DeleteDict)
    @RequestMapping(value = "/delete")
    @ResponseBody
    public Object delete(@RequestParam Integer payServiceId) {
        LogObjectHolder.me().set(ConstantFactory.me().getPaySerivceName(payServiceId));//缓存被删除的科室名称
        service.deleteChannel(payServiceId);
        return SUCCESS_TIP;
    }

    /**
     * 修改收费项目
     */
    @RequestMapping(value = "/payService_update/{payServiceId}")
    public String payServiceUpdate(@PathVariable String payServiceId, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(payServiceId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        PayServiceInfo info = service.selectOneById(payServiceId);
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + ShiroKit.getUser().getPay_service_grade_info() + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("payServiceInfo", info);
        return PREFIX + "payService_edit.html";
    }

    @RequestMapping(value = "/getDepartment", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getDepartment() {
        List<PayServicedInfo> payServiceInfoList = service.getPayServiceInfoList();
        return payServiceInfoList;
    }

    /**
     * 获取科室的tree列表
     */
    @RequestMapping(value = "/tree")
    @ResponseBody
    public List<ZTreeNode> tree() {
        List<ZTreeNode> tree = this.dao.tree();
        tree.add(ZTreeNode.createParent());
        return tree;
    }

    /**
     * 科室列表
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object list(String condition) {
        List<Map<String, Object>> list = this.dao.list(condition);
        return super.warpObject(new ChannelWarpper(list));
    }

    /**
     * 新增收费项目
     */
    @BussinessLog(value = "添加收费项目", key = "payServiceName", dict = Dict.PaySerivceDict)
    @RequestMapping(value = "/add")
    @ResponseBody
    public Object add(PayService info) {
        if (ToolUtil.isOneEmpty(info, info.getPayservicename())) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        channelSetPids(info);//完善pids,根据pid拿到pid的pids
        return this.mPayServiceMapper.insert(info);
    }

    private void channelSetPids(PayService info) {
        if (ToolUtil.isEmpty(info.getPid()) || "0".equals(info.getPid() + "")) {
            info.setPid(0);
            info.setPids("[0],");
        } else {
            int pid = info.getPid();
            PayService temp = mPayServiceMapper.selectById(pid);
            String pids = temp.getPids();
            info.setPid(pid);
            info.setPids(pids + "[" + pid + "],");
        }
    }

    @RequestMapping("/payServicelist")
    @ResponseBody
    public Object testrInfoList(SearchPayServiceInfo info, HttpServletRequest request) {
        Page<PayServiceInfo> page = new PageFactory<PayServiceInfo>().defaultPage();
        try {
            List<PayServiceInfo> list = service.selectPageListByQuery(page, info);
            page.setRecords(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 添加预约客户
     */
    @RequestMapping(value = "/addPayService")
    @ResponseBody
    public int add(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        return service.insert(info);
    }

    @RequestMapping(value = "/updatePayService")
    @ResponseBody
    public int update(@Valid PayServiceInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        return service.updatePayService(info);
    }

    /**
     * 删除预约客户
     */
    @RequestMapping(value = "/deletePayService/{id}")
    @ResponseBody
    public Tip delete(@PathVariable String id) throws Exception {
        service.delete(id);
        return SUCCESS_TIP;
    }

    /**
     * 项目详情
     */
    @RequestMapping(value = "/detail/{id}")
    @ResponseBody
    public Object detail(@PathVariable("id") Integer id) {
        return mPayServiceMapper.selectById(id);
    }

    /**
     * 项目详情
     */
    @RequestMapping(value = "/projectDetail", method = RequestMethod.POST)
    @ResponseBody
    public JSONObject projectDetail(String id, HttpServletRequest request) {
        PayServiceInfo payServiceInfo = service.selectOneByIdTwo(id);
        return JSONObject.fromObject(payServiceInfo);
    }

    /**
     * 修改
     */
    @BussinessLog(value = "修改收费项目", key = "payServiceName", dict = Dict.PaySerivceDict)
    @RequestMapping(value = "/update")
    @ResponseBody
    public Object update(PayService payService) {
        if (ToolUtil.isEmpty(payService) || payService.getId() == null) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        channelSetPids(payService);
        mPayServiceMapper.updateById(payService);
        return super.SUCCESS_TIP;
    }

    @RequestMapping(value = "/getList", method = RequestMethod.POST)
    @ResponseBody
    public List<Map<String, Object>> getList() {
        List<Map<String, Object>> list = service.list();
        return list;
    }

    @RequestMapping(value = "/getCouponPrice", method = RequestMethod.POST)
    @ResponseBody
    public Double getCouponPrice(String id) {
        String sql = "select coupon_price from checkout_method_info where id='" + id + "'";
        PayServiceInfo info = sqlMapper.selectOne(sql, PayServiceInfo.class);
        return info.getCouponPrice();
    }

    //修改开单项目提交
    @RequestMapping(value = "/updateOk", method = RequestMethod.POST)
    @ResponseBody
    public int updateOk(String[] str, String remarkss, HttpServletRequest request) {
        String id = "";
        String serviceActualPay = "";
        String foldingPay = "";
        for (int i = 0; i < str.length; i++) {
            id = str[i].split("#")[0];
            serviceActualPay = str[i].split("#")[1];
            foldingPay = str[i].split("#")[2];
            Double arrears = sub(Double.parseDouble(foldingPay), Double.parseDouble(serviceActualPay));
            String sql1 = "update " + ShiroKit.getUser().getDeal_info() + " a set a.folding_pay=" + foldingPay + ",a.service_actual_pay=" + serviceActualPay + ",a.arrears=" + arrears + "" +
                    ",remarks='" + remarkss + "' where a.id='" + id + "'";
            sqlMapper.update(sql1);
        }
        return 1;
    }

    //批量上架
    @RequestMapping(value = "/shangjiaPayService", method = RequestMethod.POST)
    @ResponseBody
    public String shangjiaPayService(@RequestParam(value = "ids[]") Integer[] ids) {
        for (Integer integer : ids) {
            String sql1 = "update " + ShiroKit.getUser().getPay_service_info() + " a set a.is_upper='1' where a.id='" + integer.intValue() + "'";
            sqlMapper.update(sql1);
        }
        return "1";
    }

    //批量下架
    @RequestMapping(value = "/xiajiaPayService", method = RequestMethod.POST)
    @ResponseBody
    public String xiajiaPayService(@RequestParam(value = "ids[]") Integer[] ids) {
        for (Integer integer : ids) {
            String sql1 = "update " + ShiroKit.getUser().getPay_service_info() + " a set a.is_upper='0' where a.id='" + integer.intValue() + "'";
            sqlMapper.update(sql1);
        }
        return "1";
    }

    //得到科室
    @RequestMapping(value = "/getPayService", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getPayService() {
        String sql1 = "select * from " + ShiroKit.getUser().getPay_service_info() + " a group by department";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql1, PayServicedInfo.class);
        return payServiceInfoList;
    }

    //得到一级项目
    @RequestMapping(value = "/getPayServiceOne", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getPayServiceOne(String department) {
        String sql1 = "select * from " + ShiroKit.getUser().getPay_service_info() + " a where department='" + department + "' group by first_level_project";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql1, PayServicedInfo.class);
        return payServiceInfoList;
    }

    //成交
    @RequestMapping(value = "/bargain", method = RequestMethod.POST)
    @ResponseBody
    public String bargain(@RequestParam(value = "ids[]") Integer[] ids) {
        for (Integer integer : ids) {
            String sql1 = "update " + ShiroKit.getUser().getPay_service_info() + " a set a.is_deal='1' where a.id='" + integer.intValue() + "'";
            sqlMapper.update(sql1);
        }
        return "1";
    }

    //不成交
    @RequestMapping(value = "/nobargain", method = RequestMethod.POST)
    @ResponseBody
    public String nobargain(@RequestParam(value = "ids[]") Integer[] ids) {
        for (Integer integer : ids) {
            String sql1 = "update " + ShiroKit.getUser().getPay_service_info() + " a set a.is_deal='2' where a.id='" + integer.intValue() + "'";
            sqlMapper.update(sql1);
        }
        return "2";
    }

    //前台可开单 是否是前台可开单项目：0不是，1是
    @RequestMapping(value = "/teeth", method = RequestMethod.POST)
    @ResponseBody
    public String teeth(@RequestParam(value = "ids[]") Integer[] ids) {
        for (Integer integer : ids) {
            String sql1 = "update " + ShiroKit.getUser().getPay_service_info() + " a set a.is_teeth_project='1' where a.id='" + integer.intValue() + "'";
            sqlMapper.update(sql1);
        }
        return "1";
    }

    //前台不可开单
    @RequestMapping(value = "/noteeth", method = RequestMethod.POST)
    @ResponseBody
    public String noteeth(@RequestParam(value = "ids[]") Integer[] ids) {
        for (Integer integer : ids) {
            String sql1 = "update " + ShiroKit.getUser().getPay_service_info() + " a set a.is_teeth_project='0' where a.id='" + integer.intValue() + "'";
            sqlMapper.update(sql1);
        }
        return "2";
    }

    //是否属于免费十项口腔检查 是 1
    @RequestMapping(value = "/freeService", method = RequestMethod.POST)
    @ResponseBody
    public String freeService(@RequestParam(value = "ids[]") Integer[] ids) {
        for (Integer integer : ids) {
            String sql1 = "update " + ShiroKit.getUser().getPay_service_info() + " a set a.free_service='1' where a.id='" + integer.intValue() + "'";
            sqlMapper.update(sql1);
        }
        return "1";
    }

    //是否属于免费十项口腔检查 不是 0
    @RequestMapping(value = "/nofreeService", method = RequestMethod.POST)
    @ResponseBody
    public String nofreeService(@RequestParam(value = "ids[]") Integer[] ids) {
        for (Integer integer : ids) {
            String sql1 = "update " + ShiroKit.getUser().getPay_service_info() + " a set a.free_service='0' where a.id='" + integer.intValue() + "'";
            sqlMapper.update(sql1);
        }
        return "2";
    }

    public static double sub(double d1, double d2) {
        BigDecimal bd1 = new BigDecimal(Double.toString(d1));
        BigDecimal bd2 = new BigDecimal(Double.toString(d2));
        return bd1.subtract(bd2).doubleValue();
    }

    @RequestMapping(value = "/payServiceSearch", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> payServiceSearch(String reach, String reservationsId, String treatmentUnit) {
        String sql = "";
        String str = "";
        if (StringUtil.isEmpty(treatmentUnit)) {  //没选择科室查所有科室下的收费项目
            str = "";
        } else {
            str = "and department='" + treatmentUnit + "'";
        }
        String sql2 = "select * from reservemoney_orthodontics_deal a where reservations_id='" + reservationsId + "' " +
                " and ('" + DateTimeKit.now() + "' between a.recharge_time and a.end_time) limit 1";
        CustomerInfo a = sqlMapper.selectOne(sql2, CustomerInfo.class);
        String psi = " ";
        if (a == null) {
            psi = " and a.first_level_project!='种植牙俱乐部'";
        }
        try {
            Double dCheckValue = Double.parseDouble(reach);
            if (dCheckValue instanceof Double == false) {
                sql = " select * from (select id as id ,department as department , first_level_project as firstLevelProject," +
                        " pay_service_name as payServiceName, pay_service_price_one as payServicePriceOne,pay_service_price_two as payServicePriceTwo " +
                        " from " + ShiroKit.getUser().getPay_service_info() + " a where " +
                        " (pay_service_name like '%" + reach + "%' or firstconcats(pay_service_name) like '%" + reach + "%') " + str + "" + psi +
                        "  and is_upper=1 order by FIND_IN_SET('" + treatmentUnit + "',department) desc) t " +
                        "  group by t.payServiceName order by FIND_IN_SET('" + treatmentUnit + "',department) desc";
            } else {
                sql = " select * from (select id as id ,department as department , first_level_project as firstLevelProject,pay_service_name as payServiceName, " +
                        " pay_service_price_one as payServicePriceOne,pay_service_price_two as payServicePriceTwo " +
                        " from " + ShiroKit.getUser().getPay_service_info() + " a where " +
                        " (pay_service_name like '%" + reach + "%' or pay_service_price_one=" + dCheckValue + " " +
                        " or pay_service_price_two=" + dCheckValue + " or firstconcats(pay_service_name) like '%" + reach + "%')  " + str + "" + psi +
                        " and is_upper=1 order by FIND_IN_SET('" + treatmentUnit + "',department) desc) t " +
                        " group by t.payServiceName order by FIND_IN_SET('" + treatmentUnit + "',department) desc";
            }
        } catch (NumberFormatException e) {
            sql = " select * from (select id as id ,department as department , first_level_project as firstLevelProject," +
                    " pay_service_name as payServiceName, pay_service_price_one as payServicePriceOne,pay_service_price_two as payServicePriceTwo " +
                    " from " + ShiroKit.getUser().getPay_service_info() + " a where " +
                    " (pay_service_name like '%" + reach + "%' or firstconcats(pay_service_name) like '%" + reach + "%') " + str + "" + psi +
                    " and is_upper=1 order by FIND_IN_SET('" + treatmentUnit + "',department) desc) t " +
                    " group by t.payServiceName order by FIND_IN_SET('" + treatmentUnit + "',department) desc";
        }
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceInfoList;
    }

    @RequestMapping(value = "/deleteDealInfos", method = RequestMethod.POST)
    @ResponseBody
    public int deleteDealInfos(String id2, Model model) throws Exception {
        String sql = "select id as id,id2 as id2,reservations_id as reservationsId,  (folding_pay - sum(service_actual_pay))  as arrears, " +
                "sum(service_actual_pay) as serviceActualPay," +
                "get_time as getTime  from " + ShiroKit.getUser().getDeal_info() + " where id2='" + id2 + "'  ";
        PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql, PayServiceInfo.class);
        String sql3 = "select id as id,id2 as id2,reservations_id as reservationsId, arrears, service_actual_pay as serviceActualPay," +
                "get_time as getTime  from " + ShiroKit.getUser().getDeal_info() + " where id2='" + id2 + "' order by getTime desc limit 0,1  ";
        PayServiceInfo payServiceInfo3 = sqlMapper.selectOne(sql3, PayServiceInfo.class);//最后一次操作得到欠费
        String deletesql = "delete from " + ShiroKit.getUser().getDeal_info() + " where id2='" + id2 + "'";
        String deletesql3 = "delete from " + ShiroKit.getUser().getRecharge_info() + " where reservations_id='" + payServiceInfo.getReservationsId() + "' and id2='" + payServiceInfo.getId2() + "' and operation_time='" + payServiceInfo.getGetTime() + "'";
        if (payServiceInfo != null) {
            String updateSql2 = "update reservations_info set money=money-'" + payServiceInfo.getServiceActualPay() + "' where id='" + payServiceInfo.getReservationsId() + "' ";
            String updateSql1 = "update " + ShiroKit.getUser().getCustomer_info() + " a set a.actual_account_balance=actual_account_balance-'" + payServiceInfo.getServiceActualPay() + "'," +
                    "account_arrears=account_arrears-'" + payServiceInfo3.getArrears() + "' where reservations_id='" + payServiceInfo.getReservationsId() + "'";
            sqlMapper.update(updateSql1);
            sqlMapper.update(updateSql2);
        }
        sqlMapper.delete(deletesql3);
        return sqlMapper.delete(deletesql);
    }

    @RequestMapping(value = "/getFirstLevelProject", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getFirstLevelProject(String dc, Model model) throws Exception {
        if (ToolUtil.isEmpty(dc.split(",")[0])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(dc.split(",")[1])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql2 = "select id from reservemoney_orthodontics_deal a where reservations_id = (select reservations_id from " + ShiroKit.getUser().getCustomer_info() + " where id='" + dc.split(",")[1] + "') " +
                " and ('" + DateTimeKit.now() + "' between a.recharge_time and a.end_time) limit 1";
        GetIdInfo getIdInfo = sqlMapper.selectOne(sql2, GetIdInfo.class);
        String psi = " ";
        if (getIdInfo == null) {
            psi = " and a.first_level_project!='种植牙俱乐部'";
        }
        String sql = "select id as id,department as department, first_level_project as firstLevelProject from " + ShiroKit.getUser().getPay_service_info() + " a where department= '" + dc.split(",")[0] + "'" +
                " and is_upper = 1 " + psi + " group by first_level_project order by id";
        List<PayServicedInfo> list =sqlMapper.selectList(sql,PayServicedInfo.class);
        return list;
    }

    @RequestMapping(value = "/getFirstLevelProjectByTreatmentUnit", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getFirstLevelProjectByTreatmentUnit(String treatmentUnit, Model model) throws Exception {
        String firstLevelByDepartmentList = treatmentUnit + "firstLevelProjectList";
        if (ShiroKit.getSessionAttr(firstLevelByDepartmentList) == null) {
            String sql = "select id as id,department as department, first_level_project as firstLevelProject from " + ShiroKit.getUser().getPay_service_info() + " a where department= '" + treatmentUnit + "'" +
                    " and is_upper = 1 group by first_level_project order by id";
            ShiroKit.setSessionAttr(firstLevelByDepartmentList, sqlMapper.selectList(sql));
            return ShiroKit.getSessionAttr(firstLevelByDepartmentList);
        } else {
            return ShiroKit.getSessionAttr(firstLevelByDepartmentList);
        }
    }

    @RequestMapping(value = "/getPaySeriveName", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getPaySeriveName(String cdf, Model model) throws Exception {
        if (ToolUtil.isEmpty(cdf.split(",")[0])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(cdf.split(",")[1])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(cdf.split(",")[2])) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String firstLevelProject = cdf.split(",")[2];
        String department = cdf.split(",")[1];
        String customerId = cdf.split(",")[0];
        List<PayServicedInfo> payServiceInfoList = service.selectListByFirstLevelProject(department, firstLevelProject);
        model.addAttribute("customerId", customerId);
        model.addAttribute("department", department);
        model.addAttribute("firstLevelProject", firstLevelProject);
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        return payServiceInfoList;
    }

    //得到免费的项目
    @RequestMapping(value = "/getPaySeriveNameFreeService", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getPaySeriveNameFreeService(String vipLevelName, Model model) throws Exception {
        String sql = "select a.id as id ,a.department as department ,a.first_level_project as firstLevelProject,a.pay_service_name as payServiceName, a.pay_service_price_one as payServicePriceOne,a.pay_service_price_two as payServicePriceTwo , a.unit as unit ,a.remarks as remarks ,\n" +
                "b.id as vipPayserviceId,b.num as vipPayserviceNum,ifnull(d.id,0) as membersPayserviceId  from yiya_wh_pay_service_info a\n" +
                " left join " + ShiroKit.getUser().getPay_service_vip() + " b on a.id=b.pay_service_id \n" +
                " left join " + ShiroKit.getUser().getVip_level_info() + " c on b.vipid=c.id\n" +
                " left join " + ShiroKit.getUser().getPay_service_members() + " d on b.id=d.vip_payservice_id\n" +
                " where c.vip_level_name='" + vipLevelName + "'";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        return payServiceInfoList;
    }

    @RequestMapping(value = "/deleteDealInfo", method = RequestMethod.POST)
    @ResponseBody
    public int deleteDealInfo(String id) throws Exception {
        String sql1 = "delete from " + ShiroKit.getUser().getDeal_info() + " where id='" + id + "'";
        return sqlMapper.delete(sql1);
    }

    /**
     * 导入
     */
    @RequestMapping(value = "/upload")
    public String importTestQuestions(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    if (!StringUtil.isEmpty(xh1)) {
                        xh1s = xh1.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh2)) {
                        xh2s = xh2.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh3)) {
                        xh3s = xh3.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh4)) {
                        xh4s = xh4.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh5)) {
                        xh5s = xh5.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh6)) {
                        xh6s = xh6.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh7)) {
                        xh7s = xh7.toString().trim();
                    }
                    if (!StringUtil.isEmpty(xh8)) {
                        xh8s = xh8.toString().trim();
                    }
                    String sql = "insert into " + ShiroKit.getUser().getPay_service_info() + " " +
                            "(num,department,first_level_project,pay_service_name,pay_service_price_one,pay_service_price_two,unit,remarks) VALUES "
                            + "('" + xh1s + "','" + xh2s + "','" + xh3s + "'" +
                            ",'" + xh4s + "','" + xh5s + "','" + xh6s + "','" + xh7s + "','" + xh8s + "');";
                    sqlMapper.insert(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "/testClose.html";
        } else {
            return "/testClose.html";
        }
    }

    @RequestMapping(value = "/openPayServiceDownLoadExcel")
    public void downLoad(HttpServletResponse response) {
        try {
            String sql = "select * from " + ShiroKit.getUser().getPay_service_info() + " order by department";
            List<PayServicedInfo> list = sqlMapper.selectList(sql, PayServicedInfo.class);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("预约数据");
            for (int i = 0; i < 8; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("id");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("num");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("department");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("first_level_project");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("pay_service_name");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("pay_service_price_one");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("pay_service_price_two");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("unit");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("remarks");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                PayServicedInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                String num = map.getNum();
                String department = map.getDepartment();
                String firstLevelProject = map.getFirstLevelProject();
                String payServiceName = map.getPayServiceName();
                Double payServicePriceOne = map.getPayServicePriceOne();
                Double payServicePriceTwo = map.getPayServicePriceTwo();
                String unit = map.getUnit();
                String id = map.getId();
                String remarks = map.getRemarks();
                for (int j = 0; j <= 9; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(id);
                row.getCell(1).setCellValue(num);
                row.getCell(2).setCellValue(department);
                row.getCell(3).setCellValue(firstLevelProject);
                row.getCell(4).setCellValue(payServiceName);
                row.getCell(5).setCellValue(payServicePriceOne);
                row.getCell(6).setCellValue(payServicePriceTwo);
                row.getCell(7).setCellValue(unit);
                row.getCell(8).setCellValue(remarks);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}